﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Odbc;
using System.Configuration;

public class OperadorDAL
{

    public int idnextval { get; set; }
    public int idOperador{ get; set; }
    public string NomeCompleto { get; set; }
    public string Cargo { get; set; }
    public string Email { get; set; }
    public string Usuario { get; set; }
    public string Senha { get; set; }

    public string Ativo { get; set; }
    public int idUpdate { get; set; }
    public string DataUpdate { get; set; }
    public int idCadastro { get; set; }
    public string DataCadastro { get; set; }
    public string msgRetorno { get; set; }
    public int codRetorno { get; set; }

    public string comandoDelete = "DELETE FROM tbOperador WHERE idOperador= ? ";
    public string comandoSelect = "SELECT * FROM tbOperador WHERE idOperador= ? ";
    public string comandoInsert;
    public string comandoUpdate;
    public int totProd;

    public OperadorDAL()
    {
        this.idOperador= next_val();

        this.NomeCompleto = "";
        this.Cargo = "";
        this.Email = "";
        this.Usuario = "";
        this.Senha = "";
        this.Ativo = "S";
        this.idUpdate = 0;
        this.DataUpdate = DateTime.Now.ToString("dd / MM / yyyy h:mm:ss");
        this.idCadastro = 0;
        this.DataCadastro = DateTime.Now.ToString("dd / MM / yyyy h:mm:ss");
    }
    public static List<OperadorDAL> SelecionarTodos(string param)
    {
        try
        {
            string comandSelectTodos = "SELECT * FROM tbOperador";

            if (param != "")
            {
                //comandSelectTodos = "SELECT * FROM tbOperadorWHERE Login LIKE '%" + param.ToString() + "%' ";
                comandSelectTodos = "SELECT * FROM tbOperador WHERE NomeCompleto = ?  ";
            }

            using (OdbcConnection con = new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            using (OdbcCommand cmd = new OdbcCommand(comandSelectTodos, con))
            {
                List<OperadorDAL> lOperador= new List<OperadorDAL>();

                cmd.Parameters.Add("@NomeCompleto", OdbcType.VarChar, 255).Value = param;
                con.Open();
                OdbcDataReader rs = cmd.ExecuteReader();
                while (rs.Read())
                {

                    OperadorDAL oObj = new OperadorDAL();
                    oObj.idOperador = Convert.ToInt32(rs["idOperador"]);
                    oObj.NomeCompleto = rs["NomeCompleto"].ToString();
                    oObj.Cargo = rs["Cargo"].ToString();
                    oObj.Email = rs["Email"].ToString();
                    oObj.Usuario = rs["nUsuario"].ToString();
                    oObj.Senha = rs["nSenha"].ToString();

                    oObj.Ativo = rs["Ativo"].ToString();
                    oObj.idUpdate = Convert.ToInt32(rs["idUpdate"]);
                    oObj.DataUpdate = rs["DataUpdate"].ToString();
                    oObj.idCadastro = Convert.ToInt32(rs["idCadastro"]);
                    oObj.DataCadastro = rs["DataCadastro"].ToString();
                    oObj.msgRetorno = "Registo encontrado";
                    oObj.codRetorno = 0;
                    lOperador.Add(oObj);
                }
                rs.Close();
                con.Close();
                return lOperador;
            }
        }
        catch (Exception ex)
        {

            List<OperadorDAL> lOperador= new List<OperadorDAL>();
            OperadorDAL oObj = new OperadorDAL();
            oObj.msgRetorno = "Registro nao encontrado";
            oObj.codRetorno = 1;
            lOperador.Add(oObj);
            return lOperador;

        }
    } // SelecionarTodos
    public int next_val()
    {
        try
        {
            using (OdbcConnection con = new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            using (OdbcCommand cmd = new OdbcCommand("SELECT TOP 1 idOperador FROM tbOperador ORDER BY idOperador DESC ", con))
            {
                idnextval = 1;
                con.Open();
                OdbcDataReader rs = cmd.ExecuteReader();
                while (rs.Read())
                {
                    idnextval = Convert.ToInt32(rs["idOperador"]) + 1;
                }
            }
            return idnextval;

        }
        catch (Exception ex)
        {
            return 0;
        }

    }
    public void SelecionarPorID(int id)
    {
        try
        {
            using (OdbcConnection con = new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            using (OdbcCommand cmd = new OdbcCommand(comandoSelect, con))
            {
                cmd.Parameters.Add("@idOperador", OdbcType.Int).Value = id;
                con.Open();
                OdbcDataReader rs = cmd.ExecuteReader();
                while (rs.Read())
                {
                    this.idOperador = Convert.ToInt32(rs["idOperador"]);
                    this.NomeCompleto = rs["NomeCompleto"].ToString();
                    this.Cargo = rs["Cargo"].ToString();
                    this.Email = rs["Email"].ToString();
                    this.Usuario = rs["nUsuario"].ToString();
                    this.Senha = rs["nSenha"].ToString();

                    this.Ativo = rs["Ativo"].ToString();
                    this.idUpdate = Convert.ToInt32(rs["idUpdate"]);
                    this.DataUpdate = rs["DataUpdate"].ToString();
                    this.idCadastro = Convert.ToInt32(rs["idCadastro"]);
                    this.DataCadastro = rs["DataCadastro"].ToString();
                    this.msgRetorno = "Registro encontrado";
                    this.codRetorno = 0;
                }
                rs.Close();
                con.Close();
            }

        }
        catch (Exception ex)
        {
            this.msgRetorno = "Registro não encontrado";
            this.codRetorno = 1;
        }
    } // SelecionarPorID
    public void Insert()
    {
        comandoInsert = "INSERT INTO tbOperador(idOperador,NomeCompleto,Cargo,Email,nUsuario,nSenha,";
        comandoInsert += "Ativo,idUpdate,idCadastro) VALUES (?,?,?,?,?,?,?,?,?) ";


        using (OdbcConnection con = new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
        using (OdbcCommand cmd = new OdbcCommand(comandoInsert, con))
        {
            cmd.Parameters.Add("@idOperador", OdbcType.VarChar, 255).Value = this.idOperador;
            cmd.Parameters.Add("@NomeCompleto", OdbcType.VarChar, 255).Value = this.NomeCompleto;
            cmd.Parameters.Add("@Cargo", OdbcType.VarChar, 255).Value = this.Cargo;
            cmd.Parameters.Add("@Email", OdbcType.VarChar, 255).Value = this.Email;
            cmd.Parameters.Add("@nUsuario", OdbcType.VarChar, 255).Value = this.Usuario;
            cmd.Parameters.Add("@nSenha", OdbcType.VarChar, 255).Value = this.Senha;

            cmd.Parameters.Add("@Ativo", OdbcType.VarChar, 255).Value = this.Ativo;
            cmd.Parameters.Add("@idUpdate", OdbcType.VarChar, 255).Value = this.idUpdate;
            cmd.Parameters.Add("@idCadastro", OdbcType.VarChar, 255).Value = this.idCadastro;

            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();

            this.msgRetorno = "Registro inserido com sucesso";
            this.codRetorno = 0;
        }
    }
    public void Update(int id)
    {
        comandoUpdate = "UPDATE tbOperador SET NomeCompleto=?,Cargo=?,Email=?,nUsuario=?,nSenha=?,Ativo=?,idUpdate=?,DataUpdate=? WHERE idOperador= ? ";

        using (OdbcConnection con = new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
        using (OdbcCommand cmd = new OdbcCommand(comandoUpdate, con))
        {

            cmd.Parameters.Add("@NomeCompleto", OdbcType.VarChar, 255).Value = this.NomeCompleto;
            cmd.Parameters.Add("@Cargo", OdbcType.VarChar, 255).Value = this.Cargo;
            cmd.Parameters.Add("@Email", OdbcType.VarChar, 255).Value = this.Email;
            cmd.Parameters.Add("@nUsuario", OdbcType.VarChar, 255).Value = this.Usuario;
            cmd.Parameters.Add("@nSenha", OdbcType.VarChar, 255).Value = this.Senha;

            cmd.Parameters.Add("@Ativo", OdbcType.VarChar, 255).Value = this.Ativo;
            cmd.Parameters.Add("@idUpdate", OdbcType.VarChar, 255).Value = this.idUpdate;
            cmd.Parameters.Add("@DataUpdate", OdbcType.VarChar, 255).Value = this.DataUpdate;
            cmd.Parameters.Add("@idOperador", OdbcType.VarChar, 255).Value = id;

            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
            this.msgRetorno = "Registro atualizado com sucesso";
            this.codRetorno = 0;
        }
    }

    public void Delete(int id)
    {
        using (OdbcConnection con = new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
        using (OdbcCommand cmd = new OdbcCommand(comandoDelete, con))
        {

            cmd.Parameters.Add("@idOperador", OdbcType.Int).Value = id;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    } //Delete

    public int totalProduto(int id)
    {
        try
        {
            using (OdbcConnection con = new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            using (OdbcCommand cmd = new OdbcCommand("SELECT COUNT(*) as totProduto FROM tbProdutoOperador WHERE idOperador= ? ", con))
            {
                cmd.Parameters.Add("@idOperador", OdbcType.Int).Value = id;
                con.Open();
                OdbcDataReader rs = cmd.ExecuteReader();
                while (rs.Read())
                {
                    totProd = Convert.ToInt32(rs["totProduto"]);
                }
                rs.Close();
                con.Close();
            }
            return totProd;

        }
        catch (Exception ex)
        {
            return 0;
        }
    } // SelecionarPorID

  public Boolean Valida(string nome, string senha)
    {
        Boolean retorno = false;
        using (OdbcConnection con = new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
        using (OdbcCommand cmd = new OdbcCommand("SELECT idOperador, nUsuario, nSenha FROM tbOperador WHERE nUsuario = ? AND nSenha = ?", con))
        {
            cmd.Parameters.Add("@nUsuario", OdbcType.VarChar, 255).Value = nome;
            cmd.Parameters.Add("@nSenha", OdbcType.VarChar, 255).Value = senha;
            con.Open();
            OdbcDataReader rs = cmd.ExecuteReader();
            while (rs.Read())
            {
                if (nome.ToString().Equals(rs["nUsuario"].ToString()) && senha.Equals(rs["nSenha"].ToString()))
                {
                    this.idOperador = Convert.ToInt32( rs["idOperador"] );
                    retorno = true;
                }
            }
        }
        return retorno;
    } // Valida
}